-------------------------------------------------------------------------------------------------
--To add a row to the table...add a new INSERT line to the file
--To change a row in the table...simply change the row's INSERT statement
--To delete a row from the table...remove the INSERT line or comment it out.  
--generated from MD3.ScriptModelData
-------------------------------------------------------------------------------------------------
SET NOCOUNT ON
--temporarily disable foreignkeys 
EXEC MD3.ForeignKeyManipulation @operation = 'DISABLE' , @tablename = 'ProductCategory' , @schemaName = 'Production'
GO
 
--build a base temp table
SELECT [ProductCategoryID],[Name],[rowguid],[ModifiedDate]
INTO #ProductCategory
FROM [Production].[ProductCategory]
WHERE 1 = 0
GO
SET IDENTITY_INSERT #ProductCategory ON
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #ProductCategory ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])VALUES(1,'Bikes','CFBDA25C-DF71-47A7-B81B-64EE161AA37C','1/1/1999')                                                                                                                      
INSERT INTO #ProductCategory ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])VALUES(2,'Components','C657828D-D808-4ABA-91A3-AF2CE02300E9','1/1/1980')                                                                                                                   
INSERT INTO #ProductCategory ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])VALUES(3,'Clothing','10A7C342-CA82-48D4-8A38-46A2EB089B74','1/1/1980')                                                                                                                     
INSERT INTO #ProductCategory ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])VALUES(4,'Accessories','2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6','1/1/1980')    
--INSERT INTO #ProductCategory ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])VALUES(5,'SomethingElse','1BE3BE36-D9A2-4EEE-B593-ED895D97C2A6','1/1/1980')   


SET IDENTITY_INSERT #ProductCategory OFF                                                                                                              

SET NOCOUNT OFF
SET IDENTITY_INSERT [Production].[ProductCategory] ON
PRINT 'Inserting New Rows (if needed) into [Production].[ProductCategory]'
INSERT INTO [Production].[ProductCategory]
     ([ProductCategoryID],[Name],[rowguid],[ModifiedDate])
SELECT temp.*
FROM #ProductCategory temp
LEFT JOIN [Production].[ProductCategory] main
     ON temp.ProductCategoryID = main.ProductCategoryID
WHERE main.ProductCategoryID IS NULL;
SET IDENTITY_INSERT [Production].[ProductCategory] OFF;
 
 
PRINT 'Deleting Old Rows (if needed) from [Production].[ProductCategory]'
DELETE FROM [Production].[ProductCategory]
FROM [Production].[ProductCategory] main
LEFT JOIN #ProductCategory temp
ON main.ProductCategoryID = temp.ProductCategoryID
WHERE temp.ProductCategoryID IS NULL;
 
 
 
PRINT 'Updating Rows that have changed from [Production].[ProductCategory]'
UPDATE [Production].[ProductCategory] SET 
[Name] = temp.[Name] ,[rowguid] = temp.[rowguid] ,[ModifiedDate] = temp.[ModifiedDate] 
FROM [Production].[ProductCategory] main
JOIN #ProductCategory temp
ON main.ProductCategoryID = temp.ProductCategoryID
JOIN	(
		SELECT ProductCategoryID, BINARY_CHECKSUM(*) AS bin_checksum FROM #ProductCategory
		EXCEPT
		SELECT ProductCategoryID, BINARY_CHECKSUM(*) AS bin_checksum FROM [Production].[ProductCategory]
		) checksm
ON main.ProductCategoryID = checksm.ProductCategoryID
 
GO
DROP TABLE #ProductCategory
GO
 
 
 
PRINT 'We are not going to re-enable the FKs in this script.  MD3 deployment will do this for us.'
PRINT 'If you want to manually run this in your DEV env then run this command:'
PRINT 'EXEC MD3.ForeignKeyManipulation @operation = ENABLE , @tablename = Blah, and @schemaname = Blah'
GO
 
 
SET NOCOUNT OFF
